<?php
/*
 * @Author: your name
 * @Date: 2022-01-20 16:26:40
 * @LastEditTime: 2022-01-25 17:20:45
 * @LastEditors: Please set LastEditors
 * @Description: 打开koroFileHeader查看配置 进行设置: https://github.com/OBKoro1/koro1FileHeader/wiki/%E9%85%8D%E7%BD%AE
 * @FilePath: /nlog/app/Models/crm_model/CrmLinkman.php
 */

namespace App\Models\crm_model;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class CrmContract extends Model
{
    use HasFactory;
    static string $tableName = 'crm_contract';
    
  
   
    public static function contractList($division_id,$whereStr,$limitStr){
        // echo " SELECT `crm_linkman`.*, `admins`.`nickName`, `admins`.`realName`, CASE WHEN `crm_linkman`.`division_id` = 0 THEN '全公司' ELSE `crm_division`.`name` END AS `division_name` FROM ( SELECT substring_index( substring_index( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', b.help_topic_id + 1 ), ',',- 1 ) AS `division_id` FROM `crm_division` AS `chain` JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH(  CONCAT( `chain`.`chain`, ',', `chain`.`id` ) ) - LENGTH( REPLACE ( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', '' )) + 1 ) WHERE `chain`.`delete_time` IS NULL AND FIND_IN_SET( ".$division_id.", CONCAT( `chain`.`chain`, ',', `chain`.`id` )) GROUP BY `division_id` ) AS `chain` INNER JOIN `crm_linkman` ON `crm_linkman`.`division_id` = `chain`.`division_id` LEFT JOIN `crm_division` ON `crm_division`.`id` = `crm_linkman`.`division_id` LEFT JOIN `admins` ON `admins`.`id` = `crm_linkman`.`admins_id` WHERE `crm_linkman`.`delete_time` IS NULL ".$whereStr." ORDER BY `id` DESC ".$limitStr;
        // echo " SELECT `crm_linkman`.*, `admins`.`nickName`, `admins`.`realName`,  `chain`.`division_name` FROM ( SELECT substring_index( substring_index( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', b.help_topic_id + 1 ), ',',- 1 ) as    `division_id`, CASE WHEN `division`.`name` is null  THEN '全公司'   ELSE  `division`.`name` END `division_name` FROM `crm_division` AS `chain` JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( CONCAT( `chain`.`chain`, ',', `chain`.`id` ) ) - LENGTH( REPLACE ( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', '' )) + 1 ) LEFT JOIN `crm_division` AS `division`    ON `division`.`id` = substring_index( substring_index( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', b.help_topic_id + 1 ), ',',- 1 )   WHERE `chain`.`delete_time` IS NULL AND FIND_IN_SET( ".$division_id.", CONCAT( `chain`.`chain`, ',', `chain`.`id` )) GROUP BY `division_id` ) AS `chain` INNER JOIN `crm_linkman` ON `crm_linkman`.`division_id` = `chain`.`division_id`  LEFT JOIN `admins` ON `admins`.`id` = `crm_linkman`.`admins_id` WHERE `crm_linkman`.`delete_time` IS NULL ".$whereStr." ORDER BY `id` DESC ".$limitStr;
     
        // echo " SELECT `crm_contract`.*, `chain`.`division_name` FROM ( SELECT substring_index( substring_index( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', b.help_topic_id + 1 ), ',',- 1 ) AS `division_id`, CASE WHEN `division`.`name` IS NULL THEN '全公司' ELSE `division`.`name` END `division_name` FROM `crm_division` AS `chain` JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( CONCAT( `chain`.`chain`, ',', `chain`.`id` ) ) - LENGTH( REPLACE ( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', '' )) + 1 ) LEFT JOIN `crm_division` AS `division` ON `division`.`id` = substring_index( substring_index( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', b.help_topic_id + 1 ), ',',- 1 ) WHERE `chain`.`delete_time` IS NULL AND FIND_IN_SET( ".$division_id.", CONCAT( `chain`.`chain`, ',', `chain`.`id` )) GROUP BY `division_id` ) AS `chain` INNER JOIN `crm_contract` ON `crm_contract`.`division_id` = `chain`.`division_id` WHERE `crm_contract`.`delete_time` IS NULL ".$whereStr." ORDER BY `id` DESC ".$limitStr;
        return DB::select( " SELECT `crm_contract`.*, `chain`.`division_name`, `admins`.`nickName`, `admins`.`realName`,`crm_product`.`product_name`, `crm_customer`.`company_name` as `customer_company_name` FROM ( SELECT substring_index( substring_index( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', b.help_topic_id + 1 ), ',',- 1 ) AS `division_id`, CASE WHEN `division`.`name` IS NULL THEN '全公司' ELSE `division`.`name` END `division_name` FROM `crm_division` AS `chain` JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( CONCAT( `chain`.`chain`, ',', `chain`.`id` ) ) - LENGTH( REPLACE ( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', '' )) + 1 ) LEFT JOIN `crm_division` AS `division` ON `division`.`id` = substring_index( substring_index( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', b.help_topic_id + 1 ), ',',- 1 ) WHERE `chain`.`delete_time` IS NULL AND FIND_IN_SET( ".$division_id.", CONCAT( `chain`.`chain`, ',', `chain`.`id` )) GROUP BY `division_id` ) AS `chain` INNER JOIN `crm_contract` ON `crm_contract`.`division_id` = `chain`.`division_id` INNER  JOIN `admins` ON `admins`.`id` = `crm_contract`.`admins_id` INNER JOIN `crm_customer` ON `crm_customer`.`id` = `crm_contract`.`customer_id` INNER JOIN `crm_product` ON `crm_contract`.`product_id` = `crm_product`.`id` WHERE `crm_contract`.`delete_time` IS NULL  ".$whereStr." ORDER BY `id` DESC ".$limitStr);
        // return DB::select(" SELECT `crm_linkman`.*,`admins`.`nickName`,`admins`.`realName`,CASE WHEN `crm_linkman`.`division_id` = 0 THEN '全公司' ELSE `crm_division`.`name` END as `division_name`  FROM ( SELECT group_concat(`chain` ) as `chain`   FROM `crm_division` WHERE `delete_time` is null AND  FIND_IN_SET(".$division_id.",`chain`) ) as `chain`   JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( `chain`.`chain` ) - LENGTH( REPLACE ( `chain`.`chain`, ',', '' )) + 1 )   INNER JOIN `crm_linkman` ON `crm_linkman`.`division_id` = substring_index( substring_index( `chain`.`chain`, ',', b.help_topic_id + 1 ), ',' ,- 1 ) LEFT JOIN `crm_division` ON `crm_division`.`id` = `crm_linkman`.`division_id` LEFT JOIN `admins` ON `admins`.`id` = `crm_linkman`.`admins_id`  WHERE `crm_linkman`.`delete_time` is null ".$whereStr." 	GROUP BY `id`  ORDER BY `id` DESC  ".$limitStr);
    }
    

    public static function contractCount($division_id,$whereStr){
       
        return DB::select(" SELECT COUNT(*) as `count` FROM ( SELECT substring_index( substring_index( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', b.help_topic_id + 1 ), ',',- 1 ) AS `division_id`, CASE WHEN `division`.`name` IS NULL THEN '全公司' ELSE `division`.`name` END `division_name` FROM `crm_division` AS `chain` JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( CONCAT( `chain`.`chain`, ',', `chain`.`id` ) ) - LENGTH( REPLACE ( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', '' )) + 1 ) LEFT JOIN `crm_division` AS `division` ON `division`.`id` = substring_index( substring_index( CONCAT( `chain`.`chain`, ',', `chain`.`id` ), ',', b.help_topic_id + 1 ), ',',- 1 ) WHERE `chain`.`delete_time` IS NULL AND FIND_IN_SET( ".$division_id.", CONCAT( `chain`.`chain`, ',', `chain`.`id` )) GROUP BY `division_id` ) AS `chain` INNER JOIN `crm_contract` ON `crm_contract`.`division_id` = `chain`.`division_id` INNER  JOIN `admins` ON `admins`.`id` = `crm_contract`.`admins_id` INNER JOIN `crm_customer` ON `crm_customer`.`id` = `crm_contract`.`customer_id` INNER JOIN `crm_product` ON `crm_contract`.`product_id` = `crm_product`.`id` WHERE `crm_contract`.`delete_time` IS NULL  ".$whereStr);
    }
   
   
}

